package top.shiyiri.preparedstatement;

import org.junit.Test;
import top.shiyiri.bean.Customer;
import top.shiyiri.util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * @author Aunean
 * @date 2022/1/17 14:06
 * @Description 针对于Customers表的查询操作
 */
public class CustomerForQuery {

    @Test
    public void testQueryForCustomers() {
        String sql = "select id, name, birth, email from customers where id = ?";
        Customer customer = queryForCustomers(sql, 8);
        System.out.println(customer);
    }
    /**
     * @Description 针对于 customers 表的通用查询操作
     */
    public Customer queryForCustomers(String sql, Object ... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
            resultSet = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //通过 ResultSetMetaData 获取结果集中的列数
            int columnCount = metaData.getColumnCount();
            if (resultSet.next()) {
                Customer customer = new Customer();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i + 1);
                    //获取
                    String columnName = metaData.getColumnName(i + 1);
                    //给 customer 对象指定的columnName属性，赋值为 value
                    Field declaredFields = Customer.class.getDeclaredField(columnName);
                    declaredFields.setAccessible(true);

                    declaredFields.set(customer, value);
                }
                return customer;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, resultSet);
        }
        return null;
    }

    @Test
    public void testQuery1() throws Exception {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();

            String sql = "select * from customers where id = ?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, 6);
            //返回结果集
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);
                System.out.println(id + "\t" + name + "\t" + email + "\t" + birth);

                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, resultSet);
        }
    }
}
